from sql import sqlseverDB
import requests,re,json,openpyxl,math,os
ms=sqlseverDB('{SQL Server}', '198.12.127.42,5810', 'SuMaiTongPol', 'sa', 'Y3GwLHaP8zQ0seZG')
smtys={
    '标题':'pname',
    '产品ID':'itemid',
    '详细参数':'attributes',
    'SKU图片网址':'skuImg',
    '价格':'skuPirce',
    'SKUID':'skuid',
    '描述':'pdec'
}

zjys={
    'Option for Variation 1':'sku1',
    'Option for Variation 2':'sku2',
}


def getsmtpro(params,everycount,filecount,btlg):

    print(f'正在获取模板 {params[0]} 的数据...')
    
    #现获取符合条件的itemid
    yntj=''
    if btlg==1:
        yntj=' and yntitle is not null'
    rp=ms.ExecQuerydict(f'''select * from ProShopeePh where [id] in (select MIN(id) from ProShopeePh where itemid in 
                         (SELECT TOP {everycount*filecount} itemid FROM (SELECT DISTINCT itemid FROM ProShopeePh
                        where pkid in (select kid from ysmbkw where mbid=?) and rating_star>=? and skuSold>=? and historical_sold>=? 
                        and cmt_count>=? and (skuPirce between ? and ?) and daochu=0{yntj})as T) GROUP BY skuid) ''',params)

    pids=[]
    
    for item in rp:
        for skuitem in ['sku1','sku2']:
            if item[skuitem]:
                if len(item[skuitem])>20:
                    item[skuitem]=(item[skuitem])[:18]
        if item['pdec']:
            if len(item['pdec'])>2500:
                item['pdec']=(item['pdec'])[:2500]
        pids.append(item['itemid'])
    
    count_dict = {}
    for item in rp:
        item_id = item['itemid']
        sku1 = item['sku1']
        sku2 = item['sku2']
        if sku1 and not sku2:
            if (item_id, sku1) in count_dict:
                count_dict[(item_id, sku1)] += 1
                item['sku1'] = f"{sku1}_{count_dict[(item_id, sku1)]}"
            else:
                count_dict[(item_id, sku1)] = 1

        if sku2:
            if (item_id,sku1,sku2) in count_dict:
                item['sku2'] = f"{sku2}_{count_dict[(item_id,sku1,sku2)]}"
                count_dict[(item_id,sku1,sku2)] += 1
            else:
                count_dict[(item_id,sku1,sku2)] = 1

    pids=list(set(pids))
    aff=0
    for j in range(math.ceil(len(pids)/1000)):
        cpids=pids[j*0:(j+1)*1000]
        wstr=','.join(['?' for _ in cpids])
        aff+=ms.ExecNoQuery(f'update ProShopeePh set daochu=daochu+1 where [itemid] in ({wstr})',cpids)

    #print(f'mbid:{params[0]},已将{aff}个产品设置为导出状态')
    
    rp_dict={}

    for r in rp:
        itemid = r['itemid']
        if rp_dict.get(itemid):
            rp_dict[itemid].append(r)
        
        else:
            rp_dict[itemid]=[r]
    
    return list(rp_dict.values())

def dc_exceldata(pros,mbid,everycount,filecount,sjname,dpnum,phl,pbs,dcmr,btlg):

    #print(f'导出份数:{filecount},每份最少量:{everycount}')
    rmb=ms.ExecQuery('select dfile from [mb] where did=?',(mbid,))
    jdpath=rmb[0][0]
    jdpaths=jdpath.split('\\')
    if not os.path.exists('官方模版表'):
        os.mkdir('官方模版表')

    mbpath=f'官方模版表/{jdpaths[-1]}'

    downpath=f'http://198.12.127.43/{jdpaths[-2]}/{jdpaths[-1]}'
    print(f'正在从服务器下载模版id:{mbid} 文件...')
    cot=requests.get(downpath).content
    with open(mbpath,'wb') as f:
        f.write(cot)
    rys=ms.ExecQuery('select * from lb where ldid=? order by lrow',(mbid,))
    xhs=math.ceil(len(pros)/everycount)

    for j in range(xhs):
        wb=openpyxl.load_workbook(mbpath)
        ws=wb['Template']
        row=7
        lowind=j*everycount
        maxind=(j+1)*everycount if (j+1)*everycount<len(pros) else len(pros)
        curprocount=maxind-lowind
        for item in pros[lowind:maxind]:
            for pro in item:
                allimgs=[pro['image']]
                if pro['images']:
                    allimgs.extend(json.loads(pro['images']))
                pro['price']=int(phl*pro['price']*pbs)
                pro['skuPirce']=int(phl*pro['skuPirce']*pbs)
                rol=1
                try:
                    for ry in rys:
                        mbcol=ry[1]
                        smtcol=ry[2]
                        gd=ry[4]
                        if '无' not in smtcol and smtcol:
                            if smtcol=='SKU名称':
                                if zjys.get(mbcol):
                                    ws.cell(row=row,column=rol).value=pro[zjys.get(mbcol)]
                                    if zjys.get(mbcol)=='sku1' and not pro['sku1']:
                                        ws.cell(row=row,column=rol).value='style'

                            elif smtcol=='主图网址':
                                if len(allimgs)>0:
                                    ws.cell(row=row,column=rol).value=allimgs.pop(0)
                            elif smtcol=='SKU图片网址':
                                if not pro['skuImg']:
                                    ws.cell(row=row,column=rol).value=pro['image']
                                else:
                                    ws.cell(row=row,column=rol).value=pro['skuImg']
                            elif smtcol=='标题' and btlg==1:
                                ws.cell(row=row,column=rol).value=pro['yntitle']
                            else:
                                ws.cell(row=row,column=rol).value=str(pro[smtys.get(smtcol)])
                        
                        else:
                            ws.cell(row=row,column=rol).value=gd
                            if gd=='size' and  not pro['sku2']:
                                ws.cell(row=row,column=rol).value=''

                        rol+=1
                    row+=1
                except Exception as e:
                    pass
        
        if not os.path.exists(f'{dcmr}/模版{mbid}导出结果'):
            os.makedirs(f'{dcmr}/模版{mbid}导出结果')

        wb.save(f'{dcmr}/模版{mbid}导出结果/{dpnum}-{sjname}-{mbid}-{curprocount}-{j+1}.xlsx')
        print(f'成功保存：{dpnum}-{sjname}-{mbid}-{curprocount}-{j+1}.xlsx')
        if j+1==filecount:
            break


